package com.z.dao.impl;

import com.z.dao.GoodsDao;
import com.z.pojo.Goods;
import com.z.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 张福兴
 * date 2021/9/4
 */
public class  GoodsDaoImpl implements GoodsDao {
    private Connection conn;
    private PreparedStatement ps;


    /**
     * 搜索商品
     *
     * @param nowpage   当前页数
     * @param eachcount 每页的数量
     * @param term      查询条件
     * @return 商品列表
     */
    @Override
    public List<Goods> selectGoodsByTid(Integer nowpage, Integer eachcount, String term) throws SQLException {
        conn = DBUtil.getConnection();
        String sql = "";
        if (term == null || "".equals(term)) {
            sql = "select * from goods limit ?,?;";

        } else {
            sql = "select * from goods where "+term+" limit ?,?;";
        }
        ps = conn.prepareStatement(sql);

        ps.setInt(1,(nowpage-1)*eachcount);
        ps.setInt(2,eachcount);


        ResultSet resultSet = ps.executeQuery();
        ArrayList<Goods> list = new ArrayList<>();
        while (resultSet.next()) {
            list.add(new Goods(
                    resultSet.getInt("gid"),
                    resultSet.getString("gname"),
                    resultSet.getDouble("price"),
                    resultSet.getInt("gnum"),
                    resultSet.getString("g_img"),
                    resultSet.getString("gdesc"),
                    resultSet.getInt("tid")
            ));
        }
        DBUtil.close(this.ps,this.conn);
        return list;
    }

    @Override
    public Integer count(String term) throws SQLException {
        this.conn = DBUtil.getConnection();
        String sql="";
        if(term==null||"".equals(term)){
            sql="select count(*) as 'count' from goods;";

        }else {
            sql="select count(*) as 'count' from goods  where "+ term+";" ;
        }
        this.ps =conn.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        Integer count = 0;
        if(resultSet.next()){
            count = resultSet.getInt("count");
        }
        DBUtil.close(ps,conn);
        return count;
    }

    @Override
    public Goods selectOneByGid(Integer gid) throws SQLException {
        conn = DBUtil.getConnection();
        String sql = "select * from goods where gid=?;";
        ps = conn.prepareStatement(sql);
        ps.setInt(1,gid);
        ResultSet resultSet = ps.executeQuery();
        Goods goods = null;
        if (resultSet.next()) {
            goods = new Goods(
                    resultSet.getInt("gid"),
                    resultSet.getString("gname"),
                    resultSet.getDouble("price"),
                    resultSet.getInt("gnum"),
                    resultSet.getString("g_img"),
                    resultSet.getString("gdesc"),
                    resultSet.getInt("tid"));
        }
        DBUtil.close(ps,conn);
        return goods;
    }

    @Override
    public Integer insertGoods(Goods goods) throws SQLException {
        conn = DBUtil.getConnection();
        String sql = "INSERT INTO `goods`(`gname`, `price`, `gnum`, `g_img`, `gdesc`, `tid`) VALUES (?,?,?,?,?,?)" ;
        ps = conn.prepareStatement(sql);
        ps.setString(1,goods.getGname());
        ps.setDouble(2,goods.getPrice());
        ps.setInt(3, goods.getGnum());
        ps.setString(4,goods.getG_img());
        ps.setString(5, goods.getGdesc());
        ps.setInt(6,goods.getTid());
        int flag = ps.executeUpdate();
        DBUtil.close(ps,conn);
        return flag;
    }


    @Override
    public Integer deleteGoods(Integer gid) throws SQLException {
        conn = DBUtil.getConnection();
        String sql = "delete from goods where gid=?";
        ps = conn.prepareStatement(sql);

        ps.setInt(1,gid);
        int i = ps.executeUpdate();
        DBUtil.close(ps,conn);
        return i;
    }

    @Override
    public Integer updataGoods(Goods goods) throws SQLException {
        this.conn = DBUtil.getConnection();
        Integer flag = 0;

        String sql="";
        if(goods.getG_img()==null||"".equals(goods.getG_img())){
            sql="update goods set gname = ? , price = ?,tid = ?,gdesc = ? where gid = ?";
        }else{
            sql="update product set gname = ? , price = ?,tid = ?,gdesc = ?,g_img = ? where gid = ?";

        }

        this.ps =conn.prepareStatement(sql);
        this.ps.setString(1,goods.getGname());
        this.ps.setDouble(2,goods.getPrice());
        this.ps.setInt(3,goods.getGid());
        this.ps.setString(4,goods.getGdesc());

        if(goods.getG_img()==null||"".equals(goods.getG_img())){

            this.ps.setInt(5,goods.getGid());
        }else {

            this.ps.setString(6,goods.getG_img());
            this.ps.setInt(7,goods.getGid());
        }
        flag = ps.executeUpdate();
        DBUtil.close(ps,conn);
        return flag;
    }

}
